#include"db.h"

// 辅助函数：根据列名获取索引
int sqlite3_column_index(sqlite3_stmt *stmt, const char *col_name) {
    int col_count = sqlite3_column_count(stmt);
    for (int i = 0; i < col_count; i++) {
        if (strcmp(sqlite3_column_name(stmt, i), col_name) == 0) {
            return i;
        }
    }
    return -1;
}

// 导出单个学生报告（修复字段索引问题）
void export_student_report(UserDatabase *db, int user_index) 
{
    char id[20];
    int is_admin = db->users[user_index].is_admin;

    if (is_admin) 
    {
        printf("\n请输入要导出的学生学号: ");
        scanf("%19s", id);
        clear_input_buffer();
    } 
    else 
    {
        strcpy(id, db->users[user_index].id);
    }
    
    const char *sql = "SELECT users.*, grades.* "
                     "FROM users "
                     "LEFT JOIN grades ON users.id = grades.id "
                     "WHERE users.id = ?;";
    
    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db->db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "准备查询失败: %s\n", sqlite3_errmsg(db->db));
        return;
    }
    
    sqlite3_bind_text(stmt, 1, id, -1, SQLITE_STATIC);
    rc = sqlite3_step(stmt);
    
    if (rc != SQLITE_ROW) {
        printf("\n未找到学生信息\n");
        sqlite3_finalize(stmt);
        return;
    }
    
    // 选择导出格式
    int format;
    printf("\n请选择导出格式:\n");
    printf("1. 文本文件 (.txt)\n");
    printf("2. CSV文件 (.csv)\n");
    printf("请选择: ");
    scanf("%d", &format);
    clear_input_buffer();
    
    if (format != 1 && format != 2) {
        printf("错误：无效选择\n");
        sqlite3_finalize(stmt);
        return;
    }
    
    // 获取学生信息
    const char *student_id = (const char*)sqlite3_column_text(stmt, 0);
    const char *username = (const char*)sqlite3_column_text(stmt, 1);
    const char *class_name = (const char*)sqlite3_column_text(stmt, 4);
    const char *phone = (const char*)sqlite3_column_text(stmt, 5);
    const char *email = (const char*)sqlite3_column_text(stmt, 6);
    
    // 获取成绩信息（如果有）- 使用列名索引替代硬编码索引
    float chinese = 0, math = 0, english = 0;
    float physics = 0, chemistry = 0, biology = 0, total = 0;
    int has_grades = 0;
    
    // 使用列名查找索引
    int chinese_idx = sqlite3_column_index(stmt, "chinese");
    int math_idx = sqlite3_column_index(stmt, "math");
    int english_idx = sqlite3_column_index(stmt, "english");
    int physics_idx = sqlite3_column_index(stmt, "physics");
    int chemistry_idx = sqlite3_column_index(stmt, "chemistry");
    int biology_idx = sqlite3_column_index(stmt, "biology");
    int total_idx = sqlite3_column_index(stmt, "total");
    
    if (chinese_idx >= 0 && sqlite3_column_type(stmt, chinese_idx) != SQLITE_NULL) {
        chinese = sqlite3_column_double(stmt, chinese_idx);
        math = sqlite3_column_double(stmt, math_idx);
        english = sqlite3_column_double(stmt, english_idx);
        physics = sqlite3_column_double(stmt, physics_idx);
        chemistry = sqlite3_column_double(stmt, chemistry_idx);
        biology = sqlite3_column_double(stmt, biology_idx);
        total = sqlite3_column_double(stmt, total_idx);
        has_grades = 1;
    }
    
    // 生成文件名
    char filename[100];
    snprintf(filename, sizeof(filename), "%s_report.%s", 
             student_id, format == 1 ? "txt" : "csv");
    
    FILE *file = fopen(filename, "w");
    if (!file) {
        perror("创建文件失败");
        sqlite3_finalize(stmt);
        return;
    }
    
    // 根据格式写入文件
    if (format == 1) { // 文本格式
        fprintf(file, "============ 学生成绩报告 ============\n\n");
        fprintf(file, "学号: %s\n", student_id);
        fprintf(file, "姓名: %s\n", username);
        fprintf(file, "班级: %s\n", class_name);
        fprintf(file, "电话: %s\n", phone);
        fprintf(file, "邮箱: %s\n\n", email);
        
        if (has_grades) {
            fprintf(file, "---------- 成绩信息 ----------\n");
            fprintf(file, "语文: %.2f\n", chinese);
            fprintf(file, "数学: %.2f\n", math);
            fprintf(file, "英语: %.2f\n", english);
            fprintf(file, "物理: %.2f\n", physics);
            fprintf(file, "化学: %.2f\n", chemistry);
            fprintf(file, "生物: %.2f\n", biology);
            fprintf(file, "总分: %.2f\n", total);
        } else {
            fprintf(file, "---------- 成绩信息 ----------\n");
            fprintf(file, "暂无成绩记录\n");
        }
        
        fprintf(file, "\n生成时间: %s\n", get_current_time());
        fprintf(file, "==================================\n");
    } else { // CSV格式
        fprintf(file, "学号,姓名,班级,电话,邮箱,");
        fprintf(file, "语文,数学,英语,物理,化学,生物,总分\n");
        
        fprintf(file, "\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",", 
                student_id, username, class_name, phone, email);
        
        if (has_grades) {
            fprintf(file, "%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f\n",
                    chinese, math, english, physics, chemistry, biology, total);
        } else {
            fprintf(file, ",,,,,,\n");
        }
    }
    
    fclose(file);
    sqlite3_finalize(stmt);
    
    printf("\n报告已成功导出到: %s\n", filename);
}

// 获取当前时间字符串
const char* get_current_time() 
{
    static char buffer[80];
    time_t now = time(NULL);
    struct tm *t = localtime(&now);
    strftime(buffer, sizeof(buffer), "%Y-%m-%d %H:%M:%S", t);
    return buffer;
}

// 导出班级报告
void export_class_report(UserDatabase *db) 
{
    char classes[50][30]; // 最多50个班级
    int class_count = get_class_list(db, classes);
    
    if (class_count == 0) {
        printf("\n暂无班级数据\n");
        return;
    }
    
    // 显示班级列表
    printf("\n=== 选择班级 ===\n");
    for (int i = 0; i < class_count; i++) {
        printf("%d. %s\n", i + 1, classes[i]);
    }
    
    int choice;
    printf("\n请选择班级: ");
    scanf("%d", &choice);
    clear_input_buffer();
    
    if (choice < 1 || choice > class_count) {
        printf("错误：无效选择\n");
        return;
    }
    
    char selected_class[30];
    strcpy(selected_class, classes[choice - 1]);
    
    // 选择导出格式
    int format;
    printf("\n请选择导出格式:\n");
    printf("1. 文本文件 (.txt)\n");
    printf("2. CSV文件 (.csv)\n");
    printf("请选择: ");
    scanf("%d", &format);
    clear_input_buffer();
    
    if (format != 1 && format != 2) {
        printf("错误：无效选择\n");
        return;
    }
    
    // 查询班级成绩
    const char *sql = "SELECT users.id, users.username, "
                     "grades.chinese, grades.math, grades.english, "
                     "grades.physics, grades.chemistry, grades.biology, grades.total "
                     "FROM users "
                     "LEFT JOIN grades ON users.id = grades.id "
                     "WHERE users.class_name = ?;";
    
    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db->db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "准备查询失败: %s\n", sqlite3_errmsg(db->db));
        return;
    }
    
    sqlite3_bind_text(stmt, 1, selected_class, -1, SQLITE_STATIC);
    
    // 初始化统计信息
    ClassStats stats = {0};
    strcpy(stats.class_name, selected_class);
    init_class_stats(&stats);
    
    // 生成文件名
    char filename[100];
    // 替换班级名称中的空格为下划线
    char safe_class_name[30];
    strcpy(safe_class_name, selected_class);
    for (int i = 0; safe_class_name[i]; i++) {
        if (safe_class_name[i] == ' ') safe_class_name[i] = '_';
    }
    
    snprintf(filename, sizeof(filename), "%s_report.%s", 
             safe_class_name, format == 1 ? "txt" : "csv");
    
    FILE *file = fopen(filename, "w");
    if (!file) {
        perror("创建文件失败");
        sqlite3_finalize(stmt);
        return;
    }
    
    // 根据格式写入文件
    if (format == 1) { // 文本格式
        fprintf(file, "============ 班级成绩报告 ============\n\n");
        fprintf(file, "班级: %s\n\n", selected_class);
        fprintf(file, "%-15s %-15s %-8s %-8s %-8s %-8s %-8s %-8s %-8s\n", 
                "学号", "姓名", "语文", "数学", "英语", "物理", "化学", "生物", "总分");
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            stats.count++;
            
            const char *id = (const char*)sqlite3_column_text(stmt, 0);
            const char *name = (const char*)sqlite3_column_text(stmt, 1);
            float chinese = sqlite3_column_double(stmt, 2);
            float math = sqlite3_column_double(stmt, 3);
            float english = sqlite3_column_double(stmt, 4);
            float physics = sqlite3_column_double(stmt, 5);
            float chemistry = sqlite3_column_double(stmt, 6);
            float biology = sqlite3_column_double(stmt, 7);
            float total = sqlite3_column_double(stmt, 8);
            
            // 写入学生成绩
            fprintf(file, "%-15s %-15s %-8.2f %-8.2f %-8.2f %-8.2f %-8.2f %-8.2f %-8.2f\n", 
                    id, name, chinese, math, english, physics, chemistry, biology, total);
            
            // 更新统计信息
            update_class_stats(&stats, chinese, math, english, physics, chemistry, biology, total);
        }
        
        // 写入统计信息
        fprintf(file, "\n=== 班级成绩统计 ===\n");
        fprintf(file, "学生人数: %d\n", stats.count);
        
        if (stats.count > 0) {
            fprintf(file, "\n总分统计:\n");
            fprintf(file, "最高分: %.2f\n", stats.max_total);
            fprintf(file, "最低分: %.2f\n", stats.min_total);
            fprintf(file, "平均分: %.2f\n", stats.total_sum / stats.count);
            
            fprintf(file, "\n语文:\n");
            fprintf(file, "最高分: %.2f\n", stats.max_chinese);
            fprintf(file, "最低分: %.2f\n", stats.min_chinese);
            fprintf(file, "平均分: %.2f\n", stats.chinese_sum / stats.count);
            
            // ... 其他科目统计类似 ...
            
            fprintf(file, "\n生成时间: %s\n", get_current_time());
        }
        
        fprintf(file, "====================================\n");
    } else { // CSV格式
        // 表头
        fprintf(file, "学号,姓名,语文,数学,英语,物理,化学,生物,总分\n");
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            stats.count++;
            
            const char *id = (const char*)sqlite3_column_text(stmt, 0);
            const char *name = (const char*)sqlite3_column_text(stmt, 1);
            float chinese = sqlite3_column_double(stmt, 2);
            float math = sqlite3_column_double(stmt, 3);
            float english = sqlite3_column_double(stmt, 4);
            float physics = sqlite3_column_double(stmt, 5);
            float chemistry = sqlite3_column_double(stmt, 6);
            float biology = sqlite3_column_double(stmt, 7);
            float total = sqlite3_column_double(stmt, 8);
            
            // 写入学生成绩
            fprintf(file, "\"%s\",\"%s\",%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f\n", 
                    id, name, chinese, math, english, physics, chemistry, biology, total);
            
            // 更新统计信息
            update_class_stats(&stats, chinese, math, english, physics, chemistry, biology, total);
        }
        
        // 写入统计信息
        fprintf(file, "\n");
        fprintf(file, "统计项,语文,数学,英语,物理,化学,生物,总分\n");
        fprintf(file, "最高分,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f\n",
                stats.max_chinese, stats.max_math, stats.max_english,
                stats.max_physics, stats.max_chemistry, stats.max_biology, stats.max_total);
        fprintf(file, "最低分,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f\n",
                stats.min_chinese, stats.min_math, stats.min_english,
                stats.min_physics, stats.min_chemistry, stats.min_biology, stats.min_total);
        fprintf(file, "平均分,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f\n",
                stats.chinese_sum / stats.count, stats.math_sum / stats.count, 
                stats.english_sum / stats.count, stats.physics_sum / stats.count,
                stats.chemistry_sum / stats.count, stats.biology_sum / stats.count,
                stats.total_sum / stats.count);
    }
    
    fclose(file);
    sqlite3_finalize(stmt);
    
    printf("\n班级报告已成功导出到: %s\n", filename);
}

// 初始化班级统计信息
void init_class_stats(ClassStats *stats) {
    stats->count = 0;
    stats->chinese_sum = 0;
    stats->math_sum = 0;
    stats->english_sum = 0;
    stats->physics_sum = 0;
    stats->chemistry_sum = 0;
    stats->biology_sum = 0;
    stats->total_sum = 0;
    
    stats->max_total = -1;
    stats->min_total = 1000;
    stats->max_chinese = -1;
    stats->min_chinese = 100;
    stats->max_math = -1;
    stats->min_math = 100;
    stats->max_english = -1;
    stats->min_english = 100;
    stats->max_physics = -1;
    stats->min_physics = 100;
    stats->max_chemistry = -1;
    stats->min_chemistry = 100;
    stats->max_biology = -1;
    stats->min_biology = 100;
}

// 更新班级统计信息
void update_class_stats(ClassStats *stats, float chinese, float math, float english,
                       float physics, float chemistry, float biology, float total) {
    // 更新各科总和
    stats->chinese_sum += chinese;
    stats->math_sum += math;
    stats->english_sum += english;
    stats->physics_sum += physics;
    stats->chemistry_sum += chemistry;
    stats->biology_sum += biology;
    stats->total_sum += total;
    
    // 更新最高分和最低分
    if (chinese > stats->max_chinese) stats->max_chinese = chinese;
    if (chinese < stats->min_chinese) stats->min_chinese = chinese;
    
    if (math > stats->max_math) stats->max_math = math;
    if (math < stats->min_math) stats->min_math = math;
    
    if (english > stats->max_english) stats->max_english = english;
    if (english < stats->min_english) stats->min_english = english;
    
    if (physics > stats->max_physics) stats->max_physics = physics;
    if (physics < stats->min_physics) stats->min_physics = physics;
    
    if (chemistry > stats->max_chemistry) stats->max_chemistry = chemistry;
    if (chemistry < stats->min_chemistry) stats->min_chemistry = chemistry;
    
    if (biology > stats->max_biology) stats->max_biology = biology;
    if (biology < stats->min_biology) stats->min_biology = biology;
    
    if (total > stats->max_total) stats->max_total = total;
    if (total < stats->min_total) stats->min_total = total;
}
